package com.founder.chd_daily_query.repo.query;

import com.founder.chd_daily_query.entity.query.LandCoalPurchasingplanEntity;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface LandCoalPurchasingplanRepo extends JpaRepository<LandCoalPurchasingplanEntity,String >, JpaSpecificationExecutor<LandCoalPurchasingplanEntity> {

    @Query(nativeQuery = true,value ="SELECT\n" +
            "\tCONTRACT_CODE,\n" +
            "\tPRICE,\n" +
            "\tCONTRACT_AMOUNT,\n" +
            "\tQNET,\n" +
            "\tSTANDARD_COAL_UNITPRICE,\n" +
            "\tEFFECTIVE_DATE_B,\n" +
            "\tEFFECTIVE_DATE_E,\n" +
            "\tCONTRACT_NAME \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tCONTRACT_ID,\n" +
            "\t\tCONTRACT_CODE,\n" +
            "\t\tTRANSPORT_TYPE,\n" +
            "\t\tEFFECTIVE_DATE_B,\n" +
            "\t\tEFFECTIVE_DATE_E,\n" +
            "\t\tRECEIVE_ORG_ID,\n" +
            "\t\tCONTRACT_AMOUNT,\n" +
            "\t\tPLAN_ATTRIBUTE,\n" +
            "\t\tQNET,\n" +
            "\t\tPRICE,\n" +
            "\t\tSTANDARD_COAL_UNITPRICE,\n" +
            "\t\tCONTRACT_NAME \n" +
            "\tFROM\n" +
            "\t\tLAND_COAL_PURCHASINGPLAN UNION ALL\n" +
            "\tSELECT\n" +
            "\t\tCONTRACT_ID,\n" +
            "\t\tCONTRACT_CODE,\n" +
            "\t\tTRANSPORT_TYPE,\n" +
            "\t\tEFFECTIVE_DATE_B,\n" +
            "\t\tEFFECTIVE_DATE_E,\n" +
            "\t\tRECEIVE_ORG_ID,\n" +
            "\t\tCONTRACT_AMOUNT,\n" +
            "\t\tPLAN_ATTRIBUTE,\n" +
            "\t\tQNET,\n" +
            "\t\tPRICE,\n" +
            "\t\tSTANDARD_COAL_UNITPRICE,\n" +
            "\t\tCONTRACT_NAME \n" +
            "\tFROM\n" +
            "\t\tWATER_COAL_PURCHASINGPLAN \n" +
            "\t) \n" +
            "WHERE\n" +
            "\tTO_CHAR( EFFECTIVE_DATE_E, 'YYYYMMDD' ) > TO_CHAR( SYSDATE, 'YYYYMMDD' ) \n" +
            "\tAND RECEIVE_ORG_ID = :power \n" +
            "\tAND PLAN_ATTRIBUTE = '市场采购' \n" +
            "ORDER BY\n" +
            "\tPRICE")
    List<Object> priorityQuery(@Param("power") String power);

    @Query(nativeQuery = true,value ="\tSELECT\n" +
            "\t\tPLAN_ATTRIBUTE,\n" +
            "\t\tsum(CONTRACT_AMOUNT)\n" +
            "\tFROM\n" +
            "\t\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tRECEIVE_ORG_ID,\n" +
            "\t\t\tCONTRACT_AMOUNT,\n" +
            "\t\t\tEFFECTIVE_DATE_E,\n" +
            "\t\t\tPLAN_ATTRIBUTE\n" +
            "\t\tFROM\n" +
            "\t\t\tLAND_COAL_PURCHASINGPLAN UNION ALL\n" +
            "\t\tSELECT\n" +
            "\t\t\tRECEIVE_ORG_ID,\n" +
            "\t\t\tCONTRACT_AMOUNT,\n" +
            "\t\t\tEFFECTIVE_DATE_E,\n" +
            "\t\t\tPLAN_ATTRIBUTE\n" +
            "\t\t\t\n" +
            "\t\tFROM\n" +
            "\t\t\tWATER_COAL_PURCHASINGPLAN \n" +
            "\t\t) \n" +
            "\tWHERE\n" +
            "\t\tTO_CHAR( EFFECTIVE_DATE_E, 'YYYYMMDD' ) > TO_CHAR( SYSDATE, 'YYYYMMDD' ) and \tRECEIVE_ORG_ID = :power  GROUP BY PLAN_ATTRIBUTE")
    List<Object> queryPercentage(@Param("power") String power);


    @Query(nativeQuery = true,value ="SELECT\n" +
            "\t\tTRANSPORT_TYPE,\n" +
            "\t\tSUM(CONTRACT_AMOUNT)\n" +
            "\tFROM\n" +
            "\t\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tCONTRACT_ID,\n" +
            "\t\t\tCONTRACT_CODE,\n" +
            "\t\t\tTRANSPORT_TYPE,\n" +
            "\t\t\tEFFECTIVE_DATE_B,\n" +
            "\t\t\tEFFECTIVE_DATE_E,\n" +
            "\t\t\tRECEIVE_ORG_ID,\n" +
            "\t\t\tCONTRACT_AMOUNT,\n" +
            "\t\t\tPLAN_ATTRIBUTE,\n" +
            "\t\t\tQNET,\n" +
            "\t\t\tPRICE,\n" +
            "\t\t\tSTANDARD_COAL_UNITPRICE \n" +
            "\t\tFROM\n" +
            "\t\t\tLAND_COAL_PURCHASINGPLAN UNION ALL\n" +
            "\t\tSELECT\n" +
            "\t\t\tCONTRACT_ID,\n" +
            "\t\t\tCONTRACT_CODE,\n" +
            "\t\t\tTRANSPORT_TYPE,\n" +
            "\t\t\tEFFECTIVE_DATE_B,\n" +
            "\t\t\tEFFECTIVE_DATE_E,\n" +
            "\t\t\tRECEIVE_ORG_ID,\n" +
            "\t\t\tCONTRACT_AMOUNT,\n" +
            "\t\t\tPLAN_ATTRIBUTE,\n" +
            "\t\t\tQNET,\n" +
            "\t\t\tPRICE,\n" +
            "\t\t\tSTANDARD_COAL_UNITPRICE \n" +
            "\t\tFROM\n" +
            "\t\t\tWATER_COAL_PURCHASINGPLAN \n" +
            "\t\t) \n" +
            "\tWHERE\n" +
            "\t\tTO_CHAR( EFFECTIVE_DATE_E, 'YYYYMMDD' ) > TO_CHAR( SYSDATE, 'YYYYMMDD' ) and \tRECEIVE_ORG_ID = :power GROUP BY TRANSPORT_TYPE")
    List<Object> queryTransport(@Param("power") String power);
}
